package model.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;

import model.dto.Player;
import utility.Database;
import utility.Log;

public class PlayerModel extends Database {
	public boolean insert(Player dto) {
		String sql = "insert into tbl_player(player_id,player_name,player_date_of_birth,player_image,player_role,club_id,country_id) values(null,?,?,?,?,?,?)";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, dto.getName());
			pstmt.setDate(2, (java.sql.Date) dto.getDateOfBirth());
			pstmt.setString(3, dto.getImage());
			pstmt.setString(4, dto.getRole());
			pstmt.setInt(5, dto.getClubId());
			pstmt.setInt(6, dto.getCountryId());
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}

		return false;
	}

	public boolean delete(int player_id) {
		String sql = "delete from tbl_player where player_id=?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, player_id);
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return false;
	}

	public boolean update(Player dto) {
		String sql = "update tbl_player set player_name=?,player_date_of_birth=?,player_image=?,player_role=?,club_id=?,country_id=? where player_id=?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, dto.getName());
			pstmt.setDate(2, (java.sql.Date) dto.getDateOfBirth());
			pstmt.setString(3, dto.getImage());
			pstmt.setString(4, dto.getRole());
			pstmt.setInt(5, dto.getClubId());
			pstmt.setInt(6, dto.getCountryId());
			pstmt.setInt(7, dto.getId());
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return false;
	}

	public Player get(int player_id) {
		String sql = "select * from tbl_player where player_id=?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, player_id);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				Player dto = new Player();
				dto.setId(player_id);
				dto.setName(rs.getString("player_name"));
				dto.setDateOfBirth(rs.getDate("player_date_of_birth"));
				dto.setImage(rs.getString("player_image"));
				dto.setRole(rs.getString("player_role"));
				dto.setClubId(rs.getInt("club_id"));
				dto.setCountryId(rs.getInt("country_id")); 
				return dto;
			}
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return null;
	}

	public ResultSet list(String search, int limit, int page) {
		String sql = "select * from tbl_player where player_name like ? ORDER BY player_id DESC limit ?,?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, "%" + search + "%");
			pstmt.setInt(2, (page - 1) * limit);
			pstmt.setInt(3, limit);
			ResultSet rs = pstmt.executeQuery();
			return rs;
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return null;
	}

	public ResultSet list(int limit, int page) {
		return list("", limit, page);
	}

	public int count(String search) {
		String sql = "select count(*) from tbl_player where player_name like ?";
		try {
			this.connect();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, "%" + search + "%");
			ResultSet rs = pstmt.executeQuery();
			return rs.next() ? rs.getInt(1) : 0;
		} catch (SQLException e) {
			Log.write(e);
		} finally {
			this.close();
		}
		return 0;
	}

	public int count() {
		return count("");
	}

	public static void main(String[] args) {
		Player dto = new Player();
		PlayerModel dao = new PlayerModel();

		dto.setId(2);
		dto.setName("Dara");
		dto.setImage("dara.png");
		dto.setCountryId(13);
		dto.setRole("Defender77");
		dto.setClubId(3);
		SimpleDateFormat df = new SimpleDateFormat("yyyy/MM/dd");
		String date = "2011/09/08";
		java.util.Date x;
		try {
			x = df.parse(date);
			dto.setDateOfBirth(new java.sql.Date(x.getTime()));
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		 System.out.println(dao.insert(dto));
		// System.out.println(dao.update(dto));
		// System.out.println(dao.delete(2));
		//System.out.println(dao.get(3).getName());
	}

}
